package com.situ.staff.score.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.staff.score.model.*;
import com.situ.staff.util.FmtEmpty;
import com.situ.staff.util.JDBCUtil;

public class ScoreDao {


	/**
	 * 添加
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int insert(ScoreModel model) throws SQLException {
		String sql = "insert into score (code_emp,code_pro,score) values(?,?,?)";
		return JDBCUtil.update(sql, model.getCodeEmp(), model.getCodePro(), model.getScore());
	}

	/**
	 * 通过号码删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int deleteById(ScoreModel model) throws SQLException {
		String sql = "delete from score where id =?";
		return JDBCUtil.update(sql.toString(), model.getId());
	}
	public int deleteByCode(ScoreModel model) throws SQLException {
		String sql = "delete from score where code_emp=? and code_pro=?";
		return JDBCUtil.update(sql.toString(), model.getCodeEmp(),model.getCodePro());
	}

	/**
	 * 删除
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int delete(ScoreModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from score");
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 更新所有字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int updateAllById(ScoreModel model) throws SQLException {
		String sql = "update score set code_emp = ? , code_pro = ?,score = ? where id =?";
		return JDBCUtil.update(sql,model.getCodeEmp(),model.getCodePro(),model.getScore() ,model.getId());
	}
	
	public int updateAll(ScoreModel model) throws SQLException {
		String sql = "update score set score = ? where code_emp=? and code_pro = ?";
		return JDBCUtil.update(sql,model.getScore(),model.getCodeEmp(),model.getCodePro());
	}

	/**
	 * 更新部分字段
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int updateActiveById(ScoreModel model) throws SQLException {
//		String sql = "update score set name = ? and tel = ? where code_emp =?";
		StringBuffer sql = new StringBuffer("update score set id=id ");
		List<Object> vals = new ArrayList<Object>();
		String codeEmp = model.getCodeEmp();
		if (codeEmp != null) {
			sql.append(" ,code_emp=?");
			vals.add(codeEmp);
		}
		String codePro = model.getCodePro();
		if (codePro != null) {
			sql.append(" ,code_pro=?");
			vals.add(codePro);
		}
		String score = model.getScore();
		if (score != null) {
			sql.append(" ,score=?");
			vals.add(score);
		}
		sql.append(" where id = ?");
		vals.add(model.getId());
		return JDBCUtil.update(sql.toString(), vals);
	}
	
	public int updateActive(ScoreModel model) throws SQLException {
//		String sql = "update score set name = ? and tel = ? where code_emp =?";
		StringBuffer sql = new StringBuffer("update score set id=id ");
		List<Object> vals = new ArrayList<Object>();
		String score = model.getScore();
		if (score != null) {
			sql.append(" ,score=?");
			vals.add(score);
		}
		sql.append(" where code_emp = ? and code_pro=?");
		vals.add(model.getCodeEmp());
		vals.add(model.getCodePro());
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 查询所有
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<ScoreModel> selectList(ScoreModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("select code_emp,code_pro,score from score ");
		List<Object> vals = appendWhere(sql, model);
//		List<String> props = Arrays.asList("code_emp", "name", "tel");
//		System.out.println(vals.toString());
		return JDBCUtil.query(sql.toString(), vals, ScoreModel.class, getFields());
	}

	/**
	 * 条件拼接和sql拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, ScoreModel model) {
		// TODO Auto-generated method stub
		List<Object> vals = new ArrayList<Object>();
		sql.append(" where 1=1 ");
//		System.out.println(model.toString());
		String codeEmp = model.getCodeEmp();
		if (!FmtEmpty.isEmpty(codeEmp)) {
			vals.add(codeEmp);
			sql.append(" and code_emp like ?");
		}
		String codePro = model.getCodePro();
		if (!FmtEmpty.isEmpty(codePro)) {
			vals.add(codePro);
			sql.append(" and code_pro like ?");
		}
		String score = model.getScore();
		if (!FmtEmpty.isEmpty(score)) {
			vals.add(score);
			sql.append(" and score like ?");
		}
		if(model.isPageOn()) {
			sql.append(" limit ?,?");
			vals.add(model.getRowStart());//limit子句的第一个参数，规定从第几条开始显示
			vals.add(model.getPageLimit());//第二个参数，规定每一页显示多少条
		}
//		System.out.println(sql.toString());
		return vals;
	}

	/**
	 * 查询个体
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public ScoreModel selectModel(ScoreModel model) throws SQLException {
		StringBuffer sql = new StringBuffer("select code_emp,code_pro,score from score");
		List<Object> vals = appendWhere(sql, model);
//		List<String> props = Arrays.asList("code_emp", "name", "tel");
		
		return JDBCUtil.queryModel(sql.toString(), vals, ScoreModel.class, getFields());
	}

	private Map<String, String> getFields() {
		Map<String, String> map = new HashMap<String, String>();
//		map.put("id","id");
		map.put("codeEmp", "code_emp");
		map.put("codePro", "code_pro");
		map.put("score","score");
		return map;
	}

	public Integer selectCount(ScoreModel m0) {
		StringBuffer sql = new StringBuffer("select count(*) from score ");
		List<Object> vals = appendWhere(sql, m0);
		
		return JDBCUtil.queryInt(sql.toString(), vals);
	}


}
